// 
// Copyright 2010 Classpath Pty Ltd <opensource@classpath.com.au>
// 
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
// 
//     http://www.apache.org/licenses/LICENSE-2.0
// 
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
// 

using build
using sql

**
** Tests the `Manager` class with a PostgreSql back end.
**
internal class ManagerWithPostgreSqlTest : Test {

    **
    ** An instance of `Manager` for testing.
    **
    Manager? manager

    **
    ** A list of [Migrations]`Migration` for testing.
    **
    Migration[]? migrations

    **
    ** An instance of `Adapter` for testing.
    **
    Adapter? adapter

    **
    ** The JDBC uri of the database being tested
    **
    Str uri := "jdbc:postgresql://172.16.203.128:5432/fantom" 
  
    **
    ** The JDBC username of the database being tested
    **
    Str username := "fantom"
  
    **
    ** The JDBC password of the database being tested
    **
    Str password := "fantom"
  
    **
    ** Drops any test tables that may have been created and left around by a broken test.
    **
    Void dropTables() {
        knownTables := ["person", "location", MigrationZero.tableName]
        sqlConn := SqlConn.open(uri, username, password)
        try {
            sqlConn.autoCommit = false
            knownTables.each |Str table| {
                if (sqlConn.meta.tableExists(table)) {
                    sqlConn.sql("DROP TABLE ${table}").execute
                }
            }
            sqlConn.commit
        }
        catch {
            sqlConn.rollback
        }
        finally {
            sqlConn.close
        }
    }

    **
    ** Performs additional setup needed for `ManagerTest`.
    **
    override Void setup() {
        super.setup()
        migrations = [ManagerWithPostgreSqlTestMigration001(), ManagerWithPostgreSqlTestMigration002(), ManagerWithPostgreSqlTestMigration003()]
        adapter = PostgreSqlAdapter()
        manager = Manager(migrations, adapter, uri, username, password)
        dropTables
    }

    **
    ** Cleans up additional setup needed for `ManagerTest`.
    **
    override Void teardown() {
        super.teardown()
        dropTables
        migrations = null
        adapter = null
        manager = null
    }

    **
    ** Tests `Manager.prepare`.
    **
    Void testPrepare() {
        manager.prepare
        sqlConn := SqlConn.open(uri, username, password)
        try {
            verifyEq(true, sqlConn.meta.tableExists(MigrationZero.tableName))
            verifyEq(1, sqlConn.sql("SELECT * FROM ${MigrationZero.tableName}").query.size)
            rows := sqlConn.sql("SELECT * FROM ${MigrationZero.tableName}").query
            verifyEq(1, rows.size)
            rows.each {
                verifyEq(4, cols.size)
                verifyEq(MigrationZero.idColumnName, cols[0].name)
                verifyEq(MigrationZero.timestampColumnName, cols[1].name)
                verifyEq(MigrationZero.fromVersionColumnName, cols[2].name)
                verifyEq(MigrationZero.toVersionColumnName, cols[3].name)
                verifyEq("1", get(col(MigrationZero.idColumnName)).toStr)
                // Don't bother checking timestamp
                verifyEq("0", get(col(MigrationZero.fromVersionColumnName)).toStr)
                verifyEq("0", get(col(MigrationZero.toVersionColumnName)).toStr)
            }
        }
        finally {
            sqlConn.close
        }
    }

    **
    ** Tests `Manager.dbVersion`
    **
    Void testDbVersion() {
        verifyNull(manager.dbVersion)
        manager.prepare
        verifyEq(Version.defVal, manager.dbVersion)
        manager.logMigration(Version("1.2.3"), Version("4.5.6"))
        verifyEq(Version("4.5.6"), manager.dbVersion)
    }

    **
    ** Tests `Manager.logMigration`
    **
    Void testLogMigration() {
        manager.prepare
        sqlConn := SqlConn.open(uri, username, password)
        try {
            manager.logMigration(Version("1.2.3"), Version("4.5.6"))
            rows := sqlConn.sql("SELECT * FROM ${MigrationZero.tableName}").query
            verifyEq(2, rows.size)
            rows[1].with {
                verifyEq("1.2.3", get(col(MigrationZero.fromVersionColumnName)).toStr)
                verifyEq("4.5.6", get(col(MigrationZero.toVersionColumnName)).toStr)
            }
        }
        finally {
            sqlConn.close
        }
    }

    **
    ** Tests 'Manager.migrate'
    **
    Void testMigrateToOne() {
        manager.prepare
        sqlConn := SqlConn.open(uri, username, password)
        try {
            sqlConn.autoCommit = false
            manager.migrate(Version.defVal, Version("1"))
            rows := sqlConn.sql("SELECT * FROM ${MigrationZero.tableName}").query
            verifyEq(2, rows.size)
            rows.last {
                verifyEq("0", get(col(MigrationZero.fromVersionColumnName)).toStr)
                verifyEq("1", get(col(MigrationZero.toVersionColumnName)).toStr)
            }
            verify(sqlConn.meta.tableExists("location"))
            sql := """INSERT INTO location (
                          location_name
                      ) VALUES (
                          @name
                      )"""
            params := ["name": "location name"]
            sqlConn.sql(sql).prepare.execute(params)
            sqlConn.commit
            rows = sqlConn.sql("SELECT * FROM location").query
            verifyEq(1, rows.size)
            rows.first {
                verifyEq(2, cols.size)
                verifyEq("location_id", cols[0].name)
                verifyEq("location_name", cols[1].name)
                verifyEq("1", get(col("location_id")).toStr)
                verifyEq("location name", get(col("location_name")).toStr)
            }
        }
        catch {
            sqlConn.rollback
        }
        finally {
            sqlConn.close
        }
    }

    **
    ** Tests 'Manager.migrate'
    **
    Void testMigrateToTwo() {
        testMigrateToOne
        manager.migrate(Version("1"), Version("2"))
        sqlConn := SqlConn.open(uri, username, password)
        try {
            sqlConn.autoCommit = false
            rows := sqlConn.sql("SELECT * FROM ${MigrationZero.tableName}").query
            verifyEq(3, rows.size)
            rows.last {
                verifyEq("1", get(col(MigrationZero.fromVersionColumnName)).toStr)
                verifyEq("2", get(col(MigrationZero.toVersionColumnName)).toStr)
            }
            verify(sqlConn.meta.tableExists("location"))
            verify(sqlConn.meta.tableExists("person"))
            sqlConn.sql("""INSERT INTO person (
                                  location_id,
                                  given_name,
                                  middle_initial,
                                  family_name,
                                  height,
                                  weight
                              ) VALUES (
                                  @location,
                                  @given,
                                  @initial,
                                  @family,
                                  @height,
                                  @weight
                              )""").prepare.execute(["location": 1,
                                                     "given": "given name",
                                                     "initial": "m",
                                                     "family": "family name",
                                                     "height": 123.45,
                                                     "weight": 678.9])
            sqlConn.commit
            rows = sqlConn.sql("SELECT * FROM person").query
            verifyEq(1, rows.size)
            rows.first {
                verifyEq(7, cols.size)
                verifyEq("person_id", cols[0].name)
                verifyEq("location_id", cols[1].name)
                verifyEq("given_name", cols[2].name)
                verifyEq("middle_initial", cols[3].name)
                verifyEq("family_name", cols[4].name)
                verifyEq("height", cols[5].name)
                verifyEq("weight", cols[6].name)
                verifyEq("1", get(col("person_id")).toStr)
                verifyEq("1", get(col("location_id")).toStr)
                verifyEq("given name", get(col("given_name")).toStr)
                verifyEq("m", get(col("middle_initial")).toStr)
                verifyEq("family name", get(col("family_name")).toStr)
                verifyEq("123.45000", get(col("height")).toStr)
                verifyEq("678.90000", get(col("weight")).toStr)
            }
        }
        catch {
            sqlConn.rollback
        }
        finally {
            sqlConn.close
        }
    }

    **
    ** Tests 'Manager.migrate'
    **
    Void testMigrateToThree() {
        testMigrateToTwo
        manager.migrate(Version("2"), Version("3"))
        sqlConn := SqlConn.open(uri, username, password)
        try {
            sqlConn.autoCommit = false
            rows := sqlConn.sql("SELECT * FROM ${MigrationZero.tableName}").query
            verifyEq(4, rows.size)
            rows.last {
                verifyEq("2", get(col(MigrationZero.fromVersionColumnName)).toStr)
                verifyEq("3", get(col(MigrationZero.toVersionColumnName)).toStr)
            }
            verify(sqlConn.meta.tableExists("location"))
            sql := """INSERT INTO location (
                          location_name,
                          latitude,
                          longitude
                      ) VALUES (
                          @name,
                          @latitude,
                          @longitude
                      )"""
            params := ["name": "location name",
                       "latitude": 123.4,
                       "longitude": 567.8]
            sqlConn.sql(sql).prepare.execute(params)
            sqlConn.commit
            rows = sqlConn.sql("SELECT * FROM location").query
            verifyEq(2, rows.size)
            rows.first {
                verifyEq(4, cols.size)
                verifyEq("location_id", cols[0].name)
                verifyEq("location_name", cols[1].name)
                verifyEq("latitude", cols[2].name)
                verifyEq("longitude", cols[3].name)
                verifyEq("1", get(col("location_id")).toStr)
                verifyEq("location name", get(col("location_name")).toStr)
                verifyEq("0.00000", get(col("latitude")).toStr)
                verifyEq("0.00000", get(col("longitude")).toStr)
            }
            rows.last {
                verifyEq(4, cols.size)
                verifyEq("location_id", cols[0].name)
                verifyEq("location_name", cols[1].name)
                verifyEq("latitude", cols[2].name)
                verifyEq("longitude", cols[3].name)
                verifyEq("2", get(col("location_id")).toStr)
                verifyEq("location name", get(col("location_name")).toStr)
                verifyEq("123.40000", get(col("latitude")).toStr)
                verifyEq("567.80000", get(col("longitude")).toStr)
            }
        }
        catch {
            sqlConn.rollback
        }
        finally {
            sqlConn.close
        }    
    }

    **
    ** Tests 'Manager.migrate'
    **
    Void testMigrateFromThree() {
        testMigrateToThree
        manager.migrate(Version("3"), Version("2"))
        sqlConn := SqlConn.open(uri, username, password)
        try {
            rows := sqlConn.sql("SELECT * FROM ${MigrationZero.tableName}").query
            verifyEq(5, rows.size)
            rows.last {
                verifyEq("3", get(col(MigrationZero.fromVersionColumnName)).toStr)
                verifyEq("2", get(col(MigrationZero.toVersionColumnName)).toStr)
            }
            verify(sqlConn.meta.tableExists("location"))
            rows = sqlConn.sql("SELECT * FROM location").query
            verifyEq(2, rows.size)
            rows.last {
                verifyEq(2, cols.size)
                verifyEq("location_id", cols[0].name)
                verifyEq("location_name", cols[1].name)
                verifyEq("2", get(col("location_id")).toStr)
                verifyEq("location name", get(col("location_name")).toStr)
            }
            verify(sqlConn.meta.tableExists("person"))
        }
        finally {
            sqlConn.close
        }
    }

    **
    ** Tests 'Manager.migrate'
    **
    Void testMigrateFromTwo() {
       testMigrateFromThree
       manager.migrate(Version("2"), Version("1"))
        sqlConn := SqlConn.open(uri, username, password)
       try {
           rows := sqlConn.sql("SELECT * FROM ${MigrationZero.tableName}").query
           verifyEq(6, rows.size)
           rows.last {
               verifyEq("2", get(col(MigrationZero.fromVersionColumnName)).toStr)
               verifyEq("1", get(col(MigrationZero.toVersionColumnName)).toStr)
           }
           verify(sqlConn.meta.tableExists("location"))
           verifyFalse(sqlConn.meta.tableExists("person"))
       }
       finally {
           sqlConn.close
       }
    }

    **
    ** Tests 'Manager.migrate'
    **
    Void testMigrateFromOne() {
        testMigrateFromTwo
        manager.migrate(Version("1"), Version.defVal)
        sqlConn := SqlConn.open(uri, username, password)
        try {
            rows := sqlConn.sql("SELECT * FROM ${MigrationZero.tableName}").query
            verifyEq(7, rows.size)
            rows.last {
                verifyEq("1", get(col(MigrationZero.fromVersionColumnName)).toStr)
                verifyEq("0", get(col(MigrationZero.toVersionColumnName)).toStr)
            }
            verifyFalse(sqlConn.meta.tableExists("location"))
            verifyFalse(sqlConn.meta.tableExists("person"))
        }
        finally {
            sqlConn.close
        }
    }

}

**
** Provides a sample `Migration` for testing.
**
internal class ManagerWithPostgreSqlTestMigration001 : Migration {

    **
    ** The `sys::Version` to `Manager.migrate` from.
    **
    override Version from := Version.defVal

    **
    ** The `sys::Version` to `Manager.migrate` to.
    **
    override Version to := Version("1")

    **
    ** Performs the upwards migration.
    **
    override Void up() {
        createTable("location") {
            createColumn("location_id", integer) { autoIncrement; primaryKey }
            createColumn("location_name", varchar(255)) { notNull }
        }
    }

    **
    ** Performs the downwards migration.
    **
    override Void down() {
        dropTable("location")
    }

}

**
** Provides a sample `Migration` for testing.
**
internal class ManagerWithPostgreSqlTestMigration002 : Migration {

    **
    ** The `sys::Version` to `Manager.migrate` from.
    **
    override Version from := Version("1")

    **
    ** The `sys::Version` to `Manager.migrate` to.
    **
    override Version to := Version("2")

    **
    ** Performs the upwards migration.
    **
    override Void up() {
        createTable("person") {
            createColumn("person_id", integer) { autoIncrement; primaryKey }
            createColumn("location_id", integer) { notNull; references("location", "location_id") }
            createColumn("given_name", varchar(255)) { notNull }
            createColumn("middle_initial", varchar(1))
            createColumn("family_name", varchar(255)) { notNull }
            createColumn("height", decimal(10, 5)) { notNull }
            createColumn("weight", decimal(10, 5)) { notNull }
            check("chk_height", "height > 0")
            check("chk_weight", "weight > 0")
        }
    }

    **
    ** Performs the downwards migration.
    **
    override Void down() {
        dropTable("person")
    }

}

**
** Provides a sample `Migration` for testing.
**
internal class ManagerWithPostgreSqlTestMigration003 : Migration {

    **
    ** The `sys::Version` to `Manager.migrate` from.
    **
    override Version from := Version("2")

    **
    ** The `sys::Version` to `Manager.migrate` to.
    **
    override Version to := Version("3")

    **
    ** Performs the upwards migration.
    **
    override Void up() {
        alterTable("location") {
            createColumn("latitude", decimal(10, 5))
            createColumn("longitude", decimal(10, 5))
        }
        executeSql("UPDATE location SET latitude = 0.0")
        executeSql("UPDATE location SET longitude = 0.0")
    }

    **
    ** Performs the downwards migration.
    **
    override Void down() {
        alterTable("location") {
            dropColumn("latitude")
            dropColumn("longitude")
        }
    }

}
